package youleche.com.sql.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import youleche.com.connection.JdbcHelper;
import youleche.com.sql.idao.ITableInfo;
import youleche.com.sql.model.MTableInfo;

/**
 * 表信息数据访问
 * @author Administrator
 *
 */
public class DTableInfo implements ITableInfo{
	/**
	 * 添加表信息
	 * @param con 数据库连接
	 * @param tableInfo 表信息
	 * @return 返回添加影响的行数
	 */
	@Override
	public int addTableInfo(Connection con, MTableInfo tableInfo) throws Exception {
		PreparedStatement pre = null;
		try{
			String sql ="insert tableinfo(tableid, tablename, tableDesc, createUser, createTime, modifyTime, databaseName) value(?,?, ?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, tableInfo.getTableId());
			pre.setObject(2, tableInfo.getTableName());
			pre.setObject(3, tableInfo.getTableDesc());
			pre.setObject(4, tableInfo.getCreateUser());
			pre.setObject(5, tableInfo.getCreateTime());
			pre.setObject(6, tableInfo.getModifyTime());
			pre.setObject(7, tableInfo.getDatabase());
			
			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 是否存在该表
	 * @param con 数据库连接
	 * @param tableName 表名
	 * @return 返回是否存在
	 * @throws Exception
	 */
	@Override
	public boolean isExistTableName(Connection con, String tableName)
			throws Exception {
		PreparedStatement ps= null;
		ResultSet rs = null;
		try{
			String sql = "select tableName from tableinfo where tableName='" + tableName + "' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					if(!"".equals(rs.getString(1))){
						return true;
					}else{
						break;
					}
				}
			}
		
			return false;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 删除表信息通过ID
	 * @param con 数据库连接
	 * @param tableId 表ID
	 * @return 返回是否删除成功
	 * @throws Exception 抛出异常
	 */
	@Override
	public boolean deleteTableInfoById(Connection con, String tableId)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update tableInfo set isDelete=1 where tableId='" + tableId + "'";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate() > 0 ? true : false;
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 获取表数据总数
	 * @param con 数据库连接
	 * @return 返回总条数
	 * @throws Exception 抛出异常
	 */
	@Override
	public int getTableInfoCount(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(tableId) from tableInfo;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getInt(1);
				}
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 分页
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 结束索引
	 * @return 返回分页数据
	 * @throws Exception 抛出异常
	 */
	@Override
	public List<MTableInfo> getTableInfoListForPage(Connection con,
			String startIndex, String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select tableId,tableName,tableDesc,createTime,modifyTime,createUser,databaseName from tableInfo where isDelete=0 order by createTime limit " + startIndex + ", " + endIndex + ";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MTableInfo tableInfo = null;
			List<MTableInfo> list = null;
			if(rs != null){
				list = new LinkedList<MTableInfo>();
				while(rs.next()){
					tableInfo = new MTableInfo();
					tableInfo.setTableId(rs.getString(1));
					tableInfo.setTableName(rs.getString(2));
					tableInfo.setTableDesc(rs.getString(3));
					tableInfo.setCreateTime(rs.getString(4));
					tableInfo.setModifyTime(rs.getString(5));
					tableInfo.setCreateUser(rs.getString(6));
					tableInfo.setDatabase(rs.getString(7));
					list.add(tableInfo);
				}
			}
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据tableID获取表数据信息
	 * @param con数据库连接
	 * @param tableId 表ID
	 * @return 表信息
	 * @throws Exception
	 */
	@Override
	public MTableInfo getTableInfoByTableId(Connection con, String tableId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select tableId,tableName,tableDesc,createTime,modifyTime,createUser,databaseName from tableInfo where isDelete=0 and tableId='"+tableId+"';";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MTableInfo tableInfo = null;
			if(rs != null){
				while(rs.next()){
					tableInfo = new MTableInfo();
					tableInfo.setTableId(rs.getString(1));
					tableInfo.setTableName(rs.getString(2));
					tableInfo.setTableDesc(rs.getString(3));
					tableInfo.setCreateTime(rs.getString(4));
					tableInfo.setModifyTime(rs.getString(5));
					tableInfo.setCreateUser(rs.getString(6));
					tableInfo.setDatabase(rs.getString(7));
					break;
				}
			}
			
			return tableInfo;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过tableId更新说明
	 * @param con数据库连接
	 * @param tableId 表ID
	 * @param tableDesc 表说明
	 * @return  返回影响的行数
	 * @throws Exception
	 */
	@Override
	public int updateTableDescByTableId(Connection con, String tableId,
			String tableDesc) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql ="update tableinfo set tabledesc='"+tableDesc+"' where tableId='"+tableId+"' and isdelete=0";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 分页查询
	 * @param con数据库连接
	 * @param condition 查询条件
	 * @param 
	 * @return 返回查询结果
	 * @throws Exception 抛出异常
	 */
	@Override
	public List<MTableInfo> getTableInfoListForQuery(Connection con,
			Map<String, String> condition, String startIndex, String pageSize) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String whereSql = "";
			Set<Map.Entry<String, String>> set = condition.entrySet();
	        for (Iterator<Map.Entry<String, String>> it = set.iterator(); it.hasNext();) {
	            Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
	            whereSql += entry.getKey() + " like '%" + entry.getValue() + "%' and ";
	        }
	        
	        if(!"".equals(whereSql)){
	        	whereSql = whereSql.substring(0, whereSql.length() - 4);
	        }
	        
			String sql = "select tableId,tableName,tableDesc,createTime,modifyTime,createUser,databaseName from tableInfo where isDelete=0 and " +
					whereSql +
					" order by createTime limit " +
					"" + startIndex + ", " + pageSize + ";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MTableInfo tableInfo = null;
			List<MTableInfo> list = null;
			if(rs != null){
				list = new LinkedList<MTableInfo>();
				while(rs.next()){
					tableInfo = new MTableInfo();
					tableInfo.setTableId(rs.getString(1));
					tableInfo.setTableName(rs.getString(2));
					tableInfo.setTableDesc(rs.getString(3));
					tableInfo.setCreateTime(rs.getString(4));
					tableInfo.setModifyTime(rs.getString(5));
					tableInfo.setCreateUser(rs.getString(6));
					tableInfo.setDatabase(rs.getString(7));
					list.add(tableInfo);
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取表数据总数(查询)
	 * @param con 数据库连接
	 * @param condition 查询条件
	 * @return 返回总条数
	 * @throws Exception 抛出异常
	 */
	@Override
	public int getTableInfoCountForQuery(Connection con,
			Map<String, String> condition) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String whereSql = "";
			Set<Map.Entry<String, String>> set = condition.entrySet();
	        for (Iterator<Map.Entry<String, String>> it = set.iterator(); it.hasNext();) {
	            Map.Entry<String, String> entry = (Map.Entry<String, String>) it.next();
	            whereSql += entry.getKey() + " like '%" + entry.getValue() + "%' and ";
	        }
	        
	        if(!"".equals(whereSql)){
	        	whereSql = whereSql.substring(0, whereSql.length() - 4);
	        }
	        
			String sql = "select count(tableId) from tableInfo where "+ whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getInt(1);
				}
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
	
	/**
	 * 根据表名获取表id
	 * @param con 数据库连接
	 * @param tableName 表名
	 * @return 返回表id
	 * @throws Exception 异常
	 */
	@Override
	public String getTableIdByTableName(Connection con, String tableName)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select tableId from tableinfo where tableName='" + tableName + "' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getString(1);
				}
			}
			
			return "";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}
}
